package cj.web.vote.dao;
/*
 *  
 *  
*/
import java.util.List;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import cj.web.vote.domain.Song;

/**
 * 
 * @author 
 *
 */
@DAO
public interface SongDAO{
	
	@SQL("SELECT * FROM Song WHERE 1=1 "
	    	    				    		        	+ " AND name=:t.name,"
	        			    		        	+ " AND description=:t.description,"
	        			    		        	+ " AND imgURL=:t.imgURL,"
	        			    		        	+ " AND songURL=:t.songURL,"
	        			    		        	+ " AND singer=:t.singer,"
	        			    		        	+ " AND university=:t.university,"
	        			    		        	+ " AND colleage=:t.colleage,"
	        			    		        	+ " AND deleteFlag=:t.deleteFlag,"
	        			    		        	+ " AND votePlus=:t.votePlus,"
	        			    		        	+ " AND createAT=:t.createAT,"
	        			    		        	+ " AND updateAT=:t.updateAT"
	        				+" ORDER BY update_date DESC LIMIT :t.start,:t.end ")
    List<Song> query(@SQLParam("t") Song song);
    
    @SQL("select * from Song where id=:id")
    Song queryById(String id);
    
    @SQL("SELECT * FROM Song WHERE id = (SELECT songId FROM VoteSongRef WHERE id = :1 LIMIT 1)")
    Song queryByVoteSongId(int voteSongId);
    
    @SQL("INSERT INTO Song ("
    		        		+ "id,"
			        		+ "name,"
			        		+ "description,"
			        		+ "imgURL,"
			        		+ "songURL,"
			        		+ "singer,"
			        		+ "university,"
			        		+ "colleage,"
			        		+ "deleteFlag,"
			        		+ "votePlus,"
			        		+ "createAT,"
			        		+ "updateAT"
							+") VALUES ("
							+ ":t.id,"
							+ ":t.name,"
							+ ":t.description,"
							+ ":t.imgURL,"
							+ ":t.songURL,"
							+ ":t.singer,"
							+ ":t.university,"
							+ ":t.colleage,"
							+ ":t.deleteFlag,"
							+ ":t.votePlus,"
							+ ":t.createAT,"
							+ ":t.updateAT"
					+")")
    void create(@SQLParam("t") Song song);
	
	@SQL("UPDATE Song SET "
		    		    						    			        + "name=:t.name,"
		        					    			        + "description=:t.description,"
		        					    			        + "imgURL=:t.imgURL,"
		        					    			        + "songURL=:t.songURL,"
		        					    			        + "singer=:t.singer,"
		        					    			        + "university=:t.university,"
		        					    			        + "colleage=:t.colleage,"
		        					    			        + "deleteFlag=:t.deleteFlag,"
		        					    			        + "votePlus=:t.votePlus,"
		        					    			        + "createAT=:t.createAT,"
		        					    			        + "updateAT=:t.updateAT"
		        						+ " WHERE id=:t.id")
    void update(@SQLParam("t") Song song);
    
    @SQL("UPDATE Song SET deleteFlag=0 WHERE id=:id")
    void deleteById(String id);
}